package com.cy.myblog.util;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 描述：Excel读写操作封装类
 *
 * */
public class ExcelUtil {

	/**************************************** 写Excel操作 **********************************************/

	/**
	 * 
	 * @param fileName
	 *            文件名 包含完整地址
	 * 
	 * @param items
	 *            操作的数据map类型集合
	 */
	public static void writeExcel(String fileName, List<Map<String, Object>> items) {
		fileName = fileName.endsWith(".xls") ? fileName : getFileName(fileName);
		excelIO(fileName, null, items, null);
	}

	/**
	 * 
	 * @param fileName
	 * @param items
	 *            任意对象类型集合
	 * @param clazz
	 *            对应的泛型
	 */
	public static <T> void writeExcel(String fileName, List<T> items, Class<?> clazz) {
		fileName = fileName.endsWith(".xls") ? fileName : getFileName(fileName);
		excelIO(fileName, items, null, clazz);
	}

	/**
	 * 
	 * @param fileName
	 * @param objects
	 * @param maps
	 * @param clazz
	 */
	private static <T> void excelIO(String fileName, List<T> objects, List<Map<String, Object>> maps, Class<?> clazz) {
		File file = new File(fileName);
		file = file.getParentFile();

		if (!file.exists()) {
			file.mkdirs();
		}

		FileOutputStream fout = null;

		try {

			fout = new FileOutputStream(fileName);
			HSSFWorkbook wb = new HSSFWorkbook(); // 创建工作簿
			HSSFSheet sheet = wb.createSheet(); // 创建工作表

			if (clazz != null)
				dataWriteToSheet(sheet, objects, wb, clazz);
			else
				dataWriteToSheet(sheet, maps, wb);

			wb.write(fout);
			fout.close();

		} catch (FileNotFoundException e) {
			e.printStackTrace();

		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @param sheet
	 * @param items
	 * @param wb
	 * @param clazz
	 */
	private static <T> void dataWriteToSheet(HSSFSheet sheet, List<T> items, HSSFWorkbook wb, Class<?> clazz) {
		// 创建表头
		HSSFRow titlerow = sheet.createRow(0);
		HSSFCell titlecell = null;

		Field field[] = clazz.getDeclaredFields();
		if (field[0].getName() == "serialVersionUID") {
			Field field2[] = new Field[field.length - 1];
			System.arraycopy(field, 1, field2, 0, field.length - 1);
			field = field2;
		}

		String[] fieldNames = new String[field.length];

		// 表头单元格样式
		HSSFCellStyle title_style = (HSSFCellStyle) wb.createCellStyle();
		HSSFCellStyle content_style = (HSSFCellStyle) wb.createCellStyle();
		// 设置表头字体
		Font font = getFontStyle(wb);
		title_style.setFont(font);
		// 设置文字居中
		title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		content_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		for (int i = 0; i < field.length; i++) {
			String fieldName = field[i].getName(); // 获取属性名
			titlecell = titlerow.createCell(i);
			sheet.setColumnWidth(i, 3766);  //设置列宽
			titlecell.setCellStyle(title_style);
			titlecell.setCellValue(fieldName);
			fieldNames[i] = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase()); // 首字母转化大写
		}

		// 创建表格内容
		try {
			int rowIndex = 1;
			int count = field.length;

			HSSFRow row = null;
			HSSFCell cell = null;
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			for (T data : items) {
				row = sheet.createRow(rowIndex);

				for (int i = 0; i < count; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(content_style);
					Method mt = clazz.getMethod("get" + fieldNames[i]); // 拼接getXxx方法
					Object value = mt.invoke(data);
					setCell(cell, sdf, value);
				}

				rowIndex++;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param sheet
	 * @param items
	 * @param wb
	 */
	private static void dataWriteToSheet(HSSFSheet sheet, List<Map<String, Object>> items, HSSFWorkbook wb) {

		// 创建表头
		HSSFRow titlerow = sheet.createRow(0);
		HSSFCell titlecell = null;
		int keyIndex = 0;
		List<String> coloums = new ArrayList<String>();

		// 表头单元格样式
		HSSFCellStyle title_style = (HSSFCellStyle) wb.createCellStyle();
		HSSFCellStyle content_style = (HSSFCellStyle) wb.createCellStyle();
		// 设置表头字体
		Font font = getFontStyle(wb);
		title_style.setFont(font);
		// 设置文字居中
		title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		content_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		for (String key : items.get(0).keySet()) {
			sheet.setColumnWidth(keyIndex, 3766);     //设置列宽
			titlecell = titlerow.createCell(keyIndex);
			titlecell.setCellStyle(title_style);
			titlecell.setCellValue(key);
			coloums.add(key);
			keyIndex++;
		}

		// 创建表内容
		try {
			int rowIndex = 1;
			int count = items.get(0).size();

			HSSFRow row = null;
			HSSFCell cell = null;
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			for (Map<String, Object> data : items) {

				row = sheet.createRow(rowIndex);

				for (int i = 0; i < count; i++) {
					cell = row.createCell(i);
					cell.setCellStyle(content_style);
					Object value = data.get(coloums.get(i));
					setCell(cell, sdf, value);
				}

				rowIndex++;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 给单元格赋值
	private static void setCell(HSSFCell cell, SimpleDateFormat sdf, Object value) {
		if (value instanceof Number)
			cell.setCellValue(((Number) value).doubleValue());
		else if (value instanceof Date)
			cell.setCellValue(sdf.format((Date) value));
		else if (value instanceof String)
			cell.setCellValue((String) value);
		else if (value instanceof Boolean)
			cell.setCellValue((Boolean) value);
		else
			cell.setCellValue(JSON.toJSONString(value));
	}

	// 表头字体样式
	private static Font getFontStyle(HSSFWorkbook wb) {
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 设置粗体
		font.setFontHeightInPoints((short) 10); // 设置字体大小
		font.setColor(HSSFColor.GREEN.index); // 设置字体颜色
		font.setFontName("微软雅黑");
		return font;
	}

	/**************************************** 读Excel操作 **********************************************/

	/**
	 * 此方法使用java反射机制
	 * 
	 * @param fileName
	 * @param clazz
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> readExcel(String fileName, Class<?> clazz) {
		fileName = getFileName(fileName);

		// 这里利用反射机制获得传入类型的属性数组.
		Field field[] = clazz.getDeclaredFields();

		if (field[0].getName() == "serialVersionUID") {
			Field field2[] = new Field[field.length - 1];
			System.arraycopy(field, 1, field2, 0, field.length - 1);
			field = field2;
		}

		String[] fieldNames = new String[field.length];
		Class[] fieldType = new Class[field.length];
		for (int i = 0; i < field.length; i++) {
			String fieldName = field[i].getName(); // getName方法可以得到属性的名称
			fieldType[i] = field[i].getType(); // getType方法可以得到属性的类型

			// 将属性名称首字母转化成大写 便于后面拼接setXxx方法
			fieldNames[i] = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
		}
		List<T> list = new ArrayList<T>();

		FileInputStream fis = null;
		HSSFWorkbook wb = null;

		try {
			fis = new FileInputStream(fileName);
			wb = new HSSFWorkbook(fis);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		// 本方法只能读取一个sheet
		HSSFSheet sheet = wb.getSheetAt(0);

		// 循环Row
		for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {

			HSSFRow row = sheet.getRow(rowNum);

			if (row == null) {
				continue;
			}

			T data = null;

			try {

				data = (T) clazz.newInstance(); // 实例化一个新的对象类型

				// 循环cell
				for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {

					HSSFCell cell = row.getCell(cellNum);

					// 利用反射动态生成setXXX方法
					Method mt = clazz.getMethod("set" + fieldNames[cellNum], fieldType[cellNum]);

					// 这里根据传入类型赋予对应的数据,若传入类型和表格对应的数据不同则会抛出异常
					if (fieldType[cellNum] == Date.class) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-DD HH:mm:ss");
						Date value = sdf.parse(getValue(cell).toString());
						mt.invoke(data, new Object[] { value }); // 动态的执行data对象类型的setXXX方法,Object[]是set方法的参数
					} else if (fieldType[cellNum] == Integer.class || fieldType[cellNum] == int.class) {
						Integer value = Integer.parseInt(getValue(cell).toString());
						mt.invoke(data, new Object[] { value });
					} else if (fieldType[cellNum] == Long.class || fieldType[cellNum] == long.class) {
						Long value = Long.parseLong(getValue(cell).toString());
						mt.invoke(data, new Object[] { value });
					} else if (fieldType[cellNum] == Boolean.class || fieldType[cellNum] == boolean.class) {
						Boolean value = Boolean.parseBoolean(getValue(cell).toString());
						mt.invoke(data, new Object[] { value });
					} else if (fieldType[cellNum] == Double.class || fieldType[cellNum] == double.class) {
						Double value = Double.parseDouble(getValue(cell).toString());
						mt.invoke(data, new Object[] { value });
					} else if (fieldType[cellNum] == Byte.class || fieldType[cellNum] == byte.class) {
						Byte value = Byte.parseByte(getValue(cell).toString());
						mt.invoke(data, new Object[] { value });
					} else {
						mt.invoke(data, new Object[] { getValue(cell) });
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			list.add(data);
		}

		try {
			wb.close(); // 低版本的poi可以不用关闭
		} catch (IOException e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * 此方法读出map<String,object>类型的数据
	 * 
	 * @param fileName
	 * @return List<Map<String, Object>>
	 */
	public static List<Map<String, Object>> readExcel(String fileName) {

		fileName = getFileName(fileName);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		String[] titles = null;
		Map<String, Object> data = null;
		FileInputStream fis = null;
		HSSFWorkbook wb = null;

		try {

			fis = new FileInputStream(fileName);
			wb = new HSSFWorkbook(fis);

		} catch (FileNotFoundException e) {
			e.printStackTrace();

		} catch (IOException e) {
			e.printStackTrace();
		}

		// 本方法只能读取一个sheet
		HSSFSheet sheet = wb.getSheetAt(0);

		// 标题部分
		HSSFRow titleRow = sheet.getRow(0);
		titles = new String[titleRow.getLastCellNum()];

		for (int i = 0; i < titleRow.getLastCellNum(); i++) {

			HSSFCell title = titleRow.getCell(i);
			titles[i] = title.getStringCellValue();

		}

		// 循环Row
		for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {

			HSSFRow row = sheet.getRow(rowNum);

			if (row == null) {
				continue;
			}

			data = new LinkedHashMap<String, Object>();

			// 循环cell
			for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {

				HSSFCell cell = row.getCell(cellNum);

				data.put(titles[cellNum], getValue(cell));
			}

			list.add(data);
		}

		try {
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

	// 返回不同类型的数值
	private static Object getValue(HSSFCell cell) {

		String regDouble = "^-?([1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*|0?\\.0+)$";
		String regInt = "^-?\\d+$";
		Pattern patternDouble = Pattern.compile(regDouble);
		Pattern patternInt = Pattern.compile(regInt);

		DecimalFormat df = new DecimalFormat("#.#########");

		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
			}
			String value = df.format(cell.getNumericCellValue());

			if (patternDouble.matcher(value).matches())
				return Double.parseDouble(value);
			else if (patternInt.matcher(value).matches())
				return Integer.parseInt(value);

			return value;
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();

		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();

		case HSSFCell.CELL_TYPE_BLANK:
			return null;

		case HSSFCell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();

		case HSSFCell.CELL_TYPE_ERROR:
			return cell.getErrorCellValue();
		}

		return null;

	}

	public static String getFileName(String fileName) {
		return fileName + ".xls";
	}
}
